package sh.wn36.vehiclemanage.mapper.mapper_cjl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;
import sh.wn36.vehiclemanage.entity.PeccancyRecord;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import sh.wn36.vehiclemanage.entity.entity_cjl.Violation;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author xk
 * @since 2021-04-26
 */
@Service
public interface PeccancyRecordMapper extends BaseMapper<PeccancyRecord> {

    @Select("SELECT p.id, c.car_no, d.`name`, dic.text, p.pec_date, p.fine, p.points, p.pec_place, p.remarks FROM peccancy_record p LEFT JOIN car c ON p.car_id = c.id LEFT JOIN driver d ON p.driver_id = d.id LEFT JOIN dictionary dic ON p.pec_option = dic.id ${ew.customSqlSegment}")
    IPage<Violation> listViolation(Page<Violation> pager,@Param(Constants.WRAPPER)QueryWrapper<Violation> wrapper);

    @Select("select * from peccancy_record where id = #{id}")
    PeccancyRecord findById(@Param("id") int id);

    @Select("SELECT \n" +
            "car.car_no ,\n" +
            "IFNULL(SUM(fine),0) AS car_expenses,\n" +
            "\"违章记录表\" AS '数据来源'\n" +
            "FROM \n" +
            "car LEFT JOIN peccancy_record ON peccancy_record.car_id = car.id \n" +
            "GROUP BY car.`car_no` " +
            "HAVING car_expenses!=0")
    List<Map<String, Object>> reportList();

    @Select("SELECT \n" +
            "car.car_no ,\n" +
            "IFNULL(SUM(fine),0) AS car_expenses,\n" +
            "\"违章记录表\" AS '数据来源'\n" +
            "FROM \n" +
            "car LEFT JOIN peccancy_record ON peccancy_record.car_id = car.id \n" +
            "where car.id=#{carId} "+
            "GROUP BY car.`car_no` " +
            "HAVING car_expenses!=0 ")
    List<Map<String, Object>> queryReportByCarId(Integer carId);

//    @Select("SELECT p.id, c.car_no, d.`name`, dic.text, p.pec_date, p.fine, p.points, p.pec_place, p.remarks FROM peccancy_record p LEFT JOIN car c ON p.car_id = c.id LEFT JOIN driver d ON p.driver_id = d.id LEFT JOIN dictionary dic ON p.pec_option = dic.id WHERE p.id = #{id}")
//    Violation findViolationsByID(@Param("id") int id);
}
